package cn.com.zsc.cm;

import java.util.Hashtable;
import java.util.Vector;

import javax.servlet.ServletException;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import cn.com.zsc.util.ConstValue;

import com.jarp.db.DAO;

/**
 * 成本管理类
 * @author 杨景
 *
 */
public class CostManage {
	/**
	 * 获取对应条数的成本数据
	 * 
	 * @param start
	 * @param limit
	 * @param cType
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public String getCost(String start, String limit, String cType) {
		String sql = "select b.CL_NAME AS SHOW_TYPE,c.NAME as SHOW_EP, a.* from CTM_COST a " +
				"left join CM_COMMLIST b on a.TYPE=b.CL_CODE left join APP_LOGIN c on a.ep=c.uid " +
				"where CTYPE='" + cType + "' order by ID desc limit " + start + "," + limit;

		DAO dao = new DAO();

		String resultStr = ConstValue.EMPTY_RECORD_PAGEING;
		
		try {
			Vector<Hashtable<String, String>> v = dao.getDataSet(ConstValue.DBNAME, sql);

			if (v != null && v.size() > 0) {
				JSONArray json = JSONArray.fromObject(v);

				String userStr = json.toString();

				sql = "select count(ID) from ctm_cost where CTYPE='" + cType+ "'";
				
				String total = dao.getDataValue(ConstValue.DBNAME, sql);
				
				resultStr = "{\"totalCount\":\"" + total + "\",\"results\":" + userStr + "}";
			}
		} catch (Exception e) {
			e.printStackTrace();
			return ConstValue.EMPTY_RECORD_PAGEING;
		}
		
		return resultStr;
	}

	/**
	 * 新增成本管理
	 * @param jso
	 * @return
	 */
	public String insertCost(String jso) {	
		JSONObject jsobject = JSONObject.fromObject(jso);
	
		String cName = jsobject.getString("cName");
		String cType = jsobject.getString("cType");
		String type = "";
		String amount = jsobject.getString("amount");
		String date = jsobject.getString("date");
		String ep = jsobject.getString("ep");
		String remarked = jsobject.getString("remarked");
		
		String newType = jsobject.getString("newType");
		
		// 新增分类
		if(!"".equals(newType)) {
			if(isExistType(newType)) {
				return "分类名【" + newType + "】已存在";
			} else {
				type = addNewType(newType);
			}
		} else {
			type = jsobject.getString("type");
		}
		
		String sql = "insert into ctm_cost values(default,'" + cName + "','"
				+ cType + "','" + type + "'," + amount + ",'" + date + "','"
				+ ep + "','" + remarked + "')";
		
		DAO dao = new DAO();
		
		try {
			int num = dao.execCommand(ConstValue.DBNAME, sql);
			
			if (num > 0) {
				return "success";
			} else {
				return "数据库操作失败";
			}
		} catch (Exception e) {
			e.printStackTrace();
			return e.getMessage();
		}
	}

	/**
	 * 修改成本管理
	 * @param jso
	 * @return
	 */
	public String updateCost(String jso) {
		JSONObject jsobject = JSONObject.fromObject(jso);
		
		String id = jsobject.getString("id");
		String cName = jsobject.getString("cName");
		String type = "";
		String amount = jsobject.getString("amount");
		String date = jsobject.getString("date");
		String ep = jsobject.getString("ep");
		String remarked = jsobject.getString("remarked");
		
		String newType = jsobject.getString("newType");
		
		// 新增分类
		if(!"".equals(newType)) {
			if(isExistType(newType)) {
				return "分类名【" + newType + "】已存在";
			} else {
				type = addNewType(newType);
			}
		} else {
			type = jsobject.getString("type");
		}
		
		String sql = "update ctm_cost set CNAME='" + cName + "',TYPE='" + type
				+ "',AMOUNT='" + amount + "',DATE='" + date + "', EP='" + ep
				+ "', REMARKED='" + remarked + "' where ID = '" + id + "'";
		
		DAO dao = new DAO();
		
		try {
			int num = dao.execCommand(ConstValue.DBNAME, sql);
			
			if (num > 0) {
				return "success";
			} else {
				return "数据库操作失败";
			}
		} catch (Exception e) {
			e.printStackTrace();
			return e.getMessage();
		}
	}

	/**
	 * 删除用户记录
	 * @param jso
	 * @return
	 */
	public String deleteCost(String jso) {
		JSONObject jsobject = JSONObject.fromObject(jso);
		
		String id = jsobject.getString("id");
		
		String delCost = "delete from ctm_cost where id ='" + id + "'";
		
		DAO dao = new DAO();
		try {
			int num = dao.execCommand(ConstValue.DBNAME, delCost);
			
			if (num > 0) {
				return "success";
			} else {
				return "数据库操作失败";
			}
		} catch (Exception e) {
			e.printStackTrace();
			return e.getMessage();
		}
	}
	
	/**
	 * 判断分类是否存在
	 * @param clname
	 * @return
	 */
	public boolean isExistType(String clname) {
		String isExistSQL = "select count(cl_name) from cm_commlist where PL_CODE='" + ConstValue.PL_CODE_29
				+ "' and cl_pcode ='" + ConstValue.CL_PCODE_MINUS_1 + "' and cl_name ='" + clname + "'";
		
		DAO dao = new DAO();
		
		String ct = null;
		
		try {
			ct = dao.getDataValue(ConstValue.DBNAME, isExistSQL);
		} catch (ServletException e) {
			e.printStackTrace();
		}
		
		if (!"0".equals(ct)) {
			return true;	
		} else {
			return false;
		}
	}
	/**
	 * 新增分类
	 * @param clname
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public String addNewType(String clname) {
		String returnCode = "";
		
		try {
			String getIdxSQL = "select count(idx) as sort from cm_commlist where PL_CODE='"
				+ ConstValue.PL_CODE_29 + "' and CL_PCODE='" + ConstValue.CL_PCODE_MINUS_1 + "'";
			
			DAO dao = new DAO();
			
			String childCount = dao.getDataValue(ConstValue.DBNAME, getIdxSQL);
			
			int index = 0;
			
			if (!"".equals(childCount))
				index = Integer.parseInt(childCount) + 1;

			String insertSQL = "insert into cm_commlist (CL_CODE,PL_CODE,CL_NAME,CL_PCODE,PARM,IDX,REMARKED)"
					+ "values (default,'" + ConstValue.PL_CODE_29 + "','" + clname + "','" + ConstValue.CL_PCODE_MINUS_1
					+ "',null,'" + index + "', null)";

			dao.execCommand(ConstValue.DBNAME, insertSQL);
			
			String queryCL_CODE = "select CL_CODE from cm_commlist where PL_CODE='" + ConstValue.PL_CODE_29 + "' and CL_NAME='" + clname + "'";
			
			Vector<Hashtable<String, String>> v = dao.getDataSet(ConstValue.DBNAME, queryCL_CODE);

			if (v != null) {
				returnCode = v.get(0).get("CL_CODE");
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			return ex.getMessage();
		}
		
		return returnCode;
	}
}
